#include <stdio.h>
#include <sys/types.h>          /* See NOTES */
#include <sys/socket.h>
#include <arpa/inet.h>
#include <netinet/in.h>
#include <pthread.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>


typedef struct Recvusermsg
{
	int code;
	char name[32];
	char password[32];
	char sex;
	int age;
}recvusermsg;

typedef struct Usersockdata
{
	int accfd;
	struct sockaddr_in cliaddr;
}usersockdata;

sqlite3 *DB;

void * Userthread(void *arg);
void insert_DB(usersockdata msg,recvusermsg recvbuf);
void login_DB(usersockdata msg,recvusermsg recvbuf);
void change_DB(usersockdata msg,recvusermsg recvbuf);
void view_DB(usersockdata msg,recvusermsg recvbuf);

int main(int argc, const char *argv[])
{
	pthread_t mythread;
	usersockdata msg;

	if(sqlite3_open("user.db",&DB)==SQLITE_OK)
	{
		printf("create/open user.db success\n");
	}
	else
	{
		printf("create/open user.db error\n");
	}

	char sql[128]={0};
	char *errmsg=NULL;

	sprintf(sql,"create table 'userdata' (username char,password char,sex char,age int)");
	
	if(sqlite3_exec(DB,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("sqlite3_exec create table error%s\n",errmsg);
	}
	else
	{
		printf("sqlite3_exec create table success\n");
	}

	int sockfd=socket(AF_INET,SOCK_STREAM,0);
	struct sockaddr_in serveraddr,clientaddr;
	serveraddr.sin_addr.s_addr=inet_addr(argv[1]);
	serveraddr.sin_port=htons(atoi(argv[2]));
	serveraddr.sin_family=AF_INET;

	socklen_t len=sizeof(serveraddr);
	bind(sockfd,(struct sockaddr *)&serveraddr,len);
	listen(sockfd,5);
	
	while(1)
	{
		int acceptfd=accept(sockfd,(struct sockaddr *)&clientaddr,&len);
		printf("client: %s %d has connected\n",inet_ntoa(clientaddr.sin_addr),ntohs(clientaddr.sin_port));
		msg.accfd=acceptfd;
		msg.cliaddr=clientaddr;
		pthread_create(&mythread,NULL,Userthread,&msg);
	}
	return 0;
}

void * Userthread(void *arg)
{
	usersockdata msg=*(usersockdata *)arg;
	recvusermsg recvbuf;
	int flagcode=0;
	while(1)
	{
		if(recv(msg.accfd,&recvbuf,sizeof(recvbuf),0)!=0)
		{
			flagcode=recvbuf.code;
			switch(flagcode)
			{
				case 1:
					insert_DB(msg,recvbuf);	
					break;
				case 2:
					login_DB(msg,recvbuf);
					break;
				case 3:
					change_DB(msg,recvbuf);
					break;
				case 4:
					view_DB(msg,recvbuf);
					break;
				default:
					break;
			}
		}

	}
}

void insert_DB(usersockdata msg,recvusermsg recvbuf)
{
	char sql[128]={0};
	char *errmsg=NULL;
	char sendbuf[128]={0};
	sprintf(sql,"insert into 'userdata' values('%s','%s','%c',%d)",recvbuf.name,recvbuf.password,recvbuf.sex,recvbuf.age);
	if(sqlite3_exec(DB,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("sqlite3_exec insert people error:%s\n",errmsg);
		sprintf(sendbuf,"1:user register error!!!");
		send(msg.accfd,sendbuf,128,0);
		printf("return register errormsg\n");
	}
	else
	{
		printf("sqlite3_exec insert people success\n");
		sprintf(sendbuf,"0:user register success!!!");
		send(msg.accfd,sendbuf,128,0);
		printf("return register successmsg\n");
	}
}

void login_DB(usersockdata msg,recvusermsg recvbuf)
{
	char sql[128]={0};
	char *errmsg=NULL;
	char sendbuf[128]={0};
	char **result=NULL;
	int nums,columns;
	sprintf(sql,"select * from 'userdata' where username = '%s'",recvbuf.name);

	sqlite3_get_table(DB,sql,&result,&nums,&columns,&errmsg);
	printf("nums=%d,columns=%d\n",nums,columns);

	if(strcmp(result[5],recvbuf.password)==0)
	{	
		sprintf(sendbuf,"2:login success\n");
		send(msg.accfd,sendbuf,128,0);
		printf("return login success \n");
	}
	else
	{
		sprintf(sendbuf,"3:login error\n");
		send(msg.accfd,sendbuf,128,0);
		printf("return login error \n");
	}
	sqlite3_free_table(result);
}

void change_DB(usersockdata msg,recvusermsg recvbuf)
{
	char sql[128]={0};
	char *errmsg=NULL;
	char sendbuf[128]={0};
	sprintf(sql,"update 'userdata' set password='%s' where username='%s'",recvbuf.password,recvbuf.name);
	if(sqlite3_exec(DB,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("sqlite3_exec updata password error\n:%s",errmsg);
		sprintf(sendbuf,"4:user updata password error!!!");
		send(msg.accfd,sendbuf,128,0);
		printf("return updata password errmsg\n");
	}
	else
	{
		printf("sqlite3_exec updata password success\n");
		sprintf(sendbuf,"5:user updata password success!!!");
		send(msg.accfd,sendbuf,128,0);
		printf("return updata password succmsg\n");
	}
}

void view_DB(usersockdata msg,recvusermsg recvbuf)
{
	char sql[128]={0};
	char *errmsg=NULL;
	char sendbuf[128]={0};
	char **result=NULL;
	int nums,columns;
	sprintf(sql,"select * from 'userdata' where username = '%s'",recvbuf.name);

	if(sqlite3_get_table(DB,sql,&result,&nums,&columns,&errmsg)!=SQLITE_OK)
	{
		printf("get table view error :%s\n",errmsg);
	}
	printf("nums=%d,columns=%d\n",nums,columns);
	sprintf(sendbuf,"username:%s,	password:%s,	sex:%s,	age:%s",result[4],result[5],result[6],result[7]);
	send(msg.accfd,sendbuf,128,0);
	printf("return viewdata success \n");
	sqlite3_free_table(result);

}

